from django.shortcuts import render
from django.http import HttpResponse

# Create your views here.
from .models import Book, Author, BookOrder
from django.db.models import Avg, Count, Sum, F, Q
from django.db import connection
from django.db.models.manager import Manager
from django.db.models.query import QuerySet


# def index(request):
#     # 获取所有图书的定价的平均价
#     result = Book.objects.aggregate(avg=Avg("price"))
#     print(result)
#     print(connection.queries)
#     return HttpResponse("index")
#
# def index2(request):
#     # 我要获取每一本图书销售的价格
#     books = Book.objects.annotate(avg=Avg("bookorder__price"))
#     for book in books:
#         print(f"{book.name}/{book.avg}")
#     # print(result)
#     print(connection.queries)
#     return HttpResponse("index2")
#
# def index3(request):
#     # book表中总共有多少本书，book表中总共有多少个id
#     # result = Book.objects.aggregate(book_nums=Count('id'))
#
#     # 统计作者表中总共有多少个不同的邮箱
#     # result = Author.objects.aggregate(email_nums=Count('email'))
#
#     # 统计每本书的销量
#     books = Book.objects.annotate(book_nums=Count("bookorder__id"))
#     for book in books:
#         print('%s/%s'%(book.name,book.book_nums))
#     print(connection.queries)
#     return HttpResponse("index3")
#
# def index4(request):
#     # 求所有图书的销售总额
#     # result = BookOrder.objects.aggregate(total=Sum('price'))
#     # print(result)
#     books = Book.objects.annotate(total=Sum('book_order__price'))
#     # print(books)
#     for book in books:
#         print(book)
#     print(connection.queries)
#     return HttpResponse("index4")
#
#
# def index6(request):
#     # Book.objects.update(price=F("price")+10)
#     # print(connection.queries[-1])
#     authors = Author.objects.filter(name=F("email"))
#     for author in authors:
#         print(f"{author.name}/{author.email}")
#     print(connection.queries[-1])
#     return HttpResponse("index6")
#
def index(request):
    print(type(Book.objects))
    return HttpResponse("index")

def index2(request):
    books = Book.objects.filter(id__gte=2).filter(~Q(id=3))
    # for book in books:
    #     print(book)
    print(type(books))
    print(books)
    return HttpResponse("index2")

def index3(request):
    # 1.根据create_time从小到大进行排序
    # orders = BookOrder.objects.order_by("create_time")
    # 2.根据create_time从大到小进行排序
    # orders = BookOrder.objects.order_by("-create_time")
    # 3.首先根据create_time从大到小进行排序，如果create_time是一样的，那么根据price从大到小进行排序
    # orders = BookOrder.objects.order_by("-create_time","-price")
    # 4.要根据订单的图书的评分来进行排序（从小到大）
    # orders = BookOrder.objects.order_by("-book__rating")
    orders = BookOrder.objects.order_by("create").order_by("price")  # 后面的order_by会覆盖前面的order_by
    return HttpResponse("index3")

def index4(request):
    books = Book.objects.values()
    print(books)
    return HttpResponse("index4")

def index5(request):
    books = Book.objects.all()
    print(books)
    return HttpResponse("index5")

def index6(request):
    books = Book.objects.select_related("author","publisher")
    for book in books:
        print(book.author.name)
        print(book.publisher.name)

    return HttpResponse("index6")

def index7(request):

    return HttpResponse("index6")